<?php

class chart {
	
	const LOCATION_ALL 		= 0;
	const LOCATION_LOCAL 	= 1;
	const LOCATION_GROUP	= 2;
	const LOCATION_USER 	= 3;
	const LOCATION_PUBLIC 	= 4;

	public static function getUserCharts($uID, $limit = 5, $personalOnly = 1){
		$dbCon = db::singleton();
		/*
		SELECT chID, book_bID, chart.group_gID, name, description, group_user.isAdmin AS groupAdmin
		FROM picboard.chart
		LEFT JOIN group_user ON chart.group_gID=group_user.group_gID
		LEFT JOIN book_chart ON chart.chID=book_chart.chart_chID
		WHERE chart.user_uID=1
		GROUP BY chID
		*/
		
		$sql = "SELECT chID, chart.group_gID, name, description, private, group_user.isAdmin AS groupAdmin FROM " . SYS_DB_DBNAME . ".chart LEFT JOIN group_user ON chart.group_gID=group_user.group_gID LEFT JOIN book_chart ON chart.chID=book_chart.chart_chID WHERE chart.user_uID=" . $dbCon->real_escape_string($uID);
		
		if($personalOnly == 1) $sql .= "  AND chart.group_gID IS NULL";
		
		$sql .= " GROUP BY chID DESC LIMIT 0, " . $dbCon->real_escape_string($limit);
		
		$result = $dbCon->single( $sql );
		
		return $result;
	}
	
	public static function getChartList($ID, $start, $end, $type = "user"){
		// Get charts for either uID or gID. Also fetch the total amount of records.

		$dbCon = db::singleton();
		/*
		SELECT chID, book_bID, chart.group_gID, name, description, group_user.isAdmin AS groupAdmin
		FROM picboard.chart
		LEFT JOIN group_user ON chart.group_gID=group_user.group_gID
		LEFT JOIN book_chart ON chart.chID=book_chart.chart_chID
		WHERE chart.user_uID=1
		GROUP BY chID
		*/
		
		switch($type){
			case "user":
				$sql = "SELECT SQL_CALC_FOUND_ROWS chID, chart.group_gID, name, description, private, group_user.isAdmin AS groupAdmin FROM " . SYS_DB_DBNAME . ".chart LEFT JOIN group_user ON chart.group_gID=group_user.group_gID LEFT JOIN book_chart ON chart.chID=book_chart.chart_chID WHERE chart.user_uID=" . $dbCon->real_escape_string($ID) . "  AND chart.group_gID IS NULL GROUP BY chID DESC LIMIT " . $dbCon->real_escape_string((int)$start) . "," . $dbCon->real_escape_string((int)$end);			
			break;
			
			case "group":
				$sql = "SELECT SQL_CALC_FOUND_ROWS chID, chart.group_gID, name, description, private, group_user.isAdmin AS groupAdmin FROM " . SYS_DB_DBNAME . ".chart LEFT JOIN group_user ON chart.group_gID=group_user.group_gID AND chart.user_uID=group_user.user_uID WHERE chart.group_gID=" . $dbCon->real_escape_string($ID) . " LIMIT " . $dbCon->real_escape_string((int)$start) . "," . $dbCon->real_escape_string((int)$end);
			break;
			
			default:
				throw new PicBoardException("no chart filter type selected");
			break;
		}
		
		$result = $dbCon->single( $sql );
		
		$numRows = $dbCon->oneRow( "SELECT FOUND_ROWS()" );
		
		return array_merge($result, $numRows);				
		
	}

	
	public static function getClones(user &$userObj, $originalID, $location = book::LOCATION_ALL){
		$dbCon = db::singleton();
		
		switch($location){
			case chart::LOCATION_ALL:
				$sql = "SELECT * FROM " . SYS_DB_DBNAME . ".chart LEFT JOIN " . SYS_DB_DBNAME . ".group ON chart.group_gID = group.gID WHERE originator_uID=" . $userObj->getuID() . " AND clone_of=" . $dbCon->real_escape_string((int)$originalID);			
			break;
			
			case chart::LOCATION_GROUP:
				$sql = "SELECT * FROM " . SYS_DB_DBNAME . ".chart LEFT JOIN " . SYS_DB_DBNAME . ".group ON chart.group_gID = group.gID WHERE originator_uID=" . $userObj->getuID() . " AND clone_of=" . $dbCon->real_escape_string((int)$originalID) . " AND group_gID IS NOT NULL";
			break;
			
			case chart::LOCATION_PUBLIC:
				$sql = "SELECT * FROM " . SYS_DB_DBNAME . ".chart LEFT JOIN " . SYS_DB_DBNAME . ".group ON chart.group_gID = group.gID WHERE clone_of=" . $dbCon->real_escape_string((int)$originalID) . " AND group_gID IS NULL AND user_uID IS NULL";			
			break;
		}
		
		$result = $dbCon->single($sql);
		
		return $result;
	}

	
	public static function isPublicDomain(user &$userObj, $chartID){
		// Is the selected chart public domain - i.e. only contains mulberry symbols.
		$chart = chart::getChart($userObj, $chartID, true);
		
		$privateArray = array();
		
		foreach($chart['data']['cells'] as $cell){
			if($cell['image_imgID'] != "" || $cell['imageURL'] != "") {
				array_push($privateArray, array("chartID" => $chartID, "cellID" => $cell['ceID'], "imgID" => $cell['image_imgID'], "imageURL" => $cell['imageURL']));
			}
		}
		
		if(count($privateArray) > 0){
			return array("status" => false, "images" => $privateArray);
		} else {
			return array("status" => true);
		}
		
	}

	public static function getUserSharableCharts($uID){
		// Charts that:
		// 1. Contain no external symbols other than mulberry
		// 2. The user is the author of the chart
		
		/*
		SELECT chID, chart.name, chart.description, chart.private, book_bID, book.name AS bookName
		FROM picboard.chart
			RIGHT JOIN (SELECT COUNT(*), chart_chID FROM picboard.cell WHERE cell.image_imgID IS NULL GROUP BY chart_chID) AS cell
			ON cell.chart_chID=chart.chID
			LEFT JOIN book_chart
			ON chart.chID=book_chart.chart_chID
			LEFT JOIN book AS book
			ON book_chart.book_bID=book.bID
		WHERE chart.user_uID=1 AND chart.group_gID IS NULL
		*/
		
		$dbCon = db::singleton();
		
		$sql = "SELECT chID, chart.name, chart.description, chart.private, book_bID, book.name AS bookName FROM " . SYS_DB_DBNAME . ".chart RIGHT JOIN (SELECT COUNT(*), chart_chID FROM " . SYS_DB_DBNAME . ".cell WHERE cell.image_imgID IS NULL GROUP BY chart_chID) AS cell ON cell.chart_chID=chart.chID LEFT JOIN book_chart ON chart.chID=book_chart.chart_chID	LEFT JOIN book AS book ON book_chart.book_bID=book.bID WHERE chart.user_uID=" . $dbCon->real_escape_string($uID) . " AND chart.group_gID IS NULL";

		$result = $dbCon->single( $sql );
		
		return $result;

	}
	
	public static function getThumbs($chID, $limit = 5){
		// Get a few thumbnails for a chart so we can use them in a slideshow.
		
		/*
		SELECT SELECT IF(mulberryURL='', imageURL, mulberryURL) AS image
		FROM picboard.cell
		LEFT JOIN picboard.image ON cell.image_imgID=image.imgID
		WHERE chart_chID=27 AND mulberryURL != "" OR chart_chID=27 AND image.imageURL IS NOT NULL
		LIMIT 0, 5
		*/

		$dbCon = db::singleton();

		$sql = "SELECT IF(mulberryURL='', imageURL, mulberryURL) AS image FROM " . SYS_DB_DBNAME . ".cell LEFT JOIN " . SYS_DB_DBNAME . ".image ON cell.image_imgID=image.imgID WHERE chart_chID=" . $dbCon->real_escape_string($chID) . " AND mulberryURL != '' OR chart_chID=" . $dbCon->real_escape_string($chID) . " AND image.imageURL IS NOT NULL LIMIT 0, " . $dbCon->real_escape_string($limit);

		$result = $dbCon->single( $sql );
		
		return $result;

	}
	
	public static function getUserChartThumbs($uID){

		/*
		SELECT IF(mulberryURL='', imageURL, mulberryURL) AS image, chart.chID
		FROM picboard.cell
		LEFT JOIN picboard.image ON cell.image_imgID=image.imgID
		LEFT JOIN picboard.chart ON cell.chart_chID=chart.chID
		WHERE chart.user_uID=1 AND mulberryURL != "" OR chart.user_uID=1 AND image.imageURL IS NOT NULL
		GROUP BY chart.chID;	
		*/	
		
		$dbCon = db::singleton();
		
		$sql = "SELECT IF(mulberryURL='', imageURL, mulberryURL) AS image, chart.chID FROM " . SYS_DB_DBNAME . ".cell LEFT JOIN " . SYS_DB_DBNAME . ".image ON cell.image_imgID=image.imgID LEFT JOIN " . SYS_DB_DBNAME . ".chart ON cell.chart_chID=chart.chID WHERE chart.user_uID=" . $dbCon->real_escape_string($uID) . " AND mulberryURL != '' OR chart.user_uID=" . $dbCon->real_escape_string($uID) . " AND image.imageURL IS NOT NULL GROUP BY chart.chID;";
	
		$result = $dbCon->single( $sql );
		
		$output = array();
		
		foreach($result as $thumb){ $output[$thumb['chID']] = $thumb['image']; }
		
		return $output;		
	
	}
	
	public static function getGroupCharts($gID, $limit = 5){
		// Get all charts associated with this group ID.
		$dbCon = db::singleton();
		
		$sql = "SELECT chID, chart.group_gID, name, description, private, group_user.isAdmin AS groupAdmin FROM " . SYS_DB_DBNAME . ".chart LEFT JOIN group_user ON chart.group_gID=group_user.group_gID AND chart.user_uID=group_user.user_uID WHERE chart.group_gID=" . $dbCon->real_escape_string($gID) . " LIMIT 0, " . $dbCon->real_escape_string($limit);
		
		$result = $dbCon->single( $sql );

		return $result;
	}
	
	public static function getGroupChartThumbs($gID){
		/*
		SELECT IF(mulberryURL='', imageURL, mulberryURL) AS image, chart.chID
		FROM picboard.cell
		LEFT JOIN picboard.image ON cell.image_imgID=image.imgID
		LEFT JOIN picboard.chart ON cell.chart_chID=chart.chID
		WHERE chart.group_gID=1 AND mulberryURL != "" OR chart.group_gID=1 AND image.imageURL IS NOT NULL
		GROUP BY chart.chID;
		*/	
		
		$dbCon = db::singleton();
		
		$sql = "SELECT IF(mulberryURL='', imageURL, mulberryURL) AS image, chart.chID FROM " . SYS_DB_DBNAME . ".cell LEFT JOIN " . SYS_DB_DBNAME . ".image ON cell.image_imgID=image.imgID LEFT JOIN " . SYS_DB_DBNAME . ".chart ON cell.chart_chID=chart.chID WHERE chart.group_gID=" . $dbCon->real_escape_string($gID) . " AND mulberryURL != '' OR chart.group_gID=" . $dbCon->real_escape_string($gID) . " AND image.imageURL IS NOT NULL GROUP BY chart.chID;";
	
		$result = $dbCon->single( $sql );
		
		$output = array();
		
		foreach($result as $thumb){ $output[$thumb['chID']] = $thumb['image']; }
		
		return $output;		
	}

	public static function getOriginator($chartID){
		$dbCon = db::singleton();

		$sql = "SELECT chart.originator_uID, user.uID, user.userName FROM " . SYS_DB_DBNAME . ".chart LEFT JOIN " . SYS_DB_DBNAME . ".user ON chart.originator_uID = user.uID WHERE chID=" . $dbCon->real_escape_string((int)$chartID);

		$result = $dbCon->oneRow( $sql );
		
		return $result;	
	}
	
	public static function chartOwnerID($chartID){
		$dbCon = db::singleton();
	
		$sql = "SELECT chart.user_uID, chart.group_gID FROM " . SYS_DB_DBNAME . ".chart WHERE chID=" . $dbCon->real_escape_string((int)$chartID);
	
		$result = $dbCon->oneRow( $sql );
		
		return $result;
	}	
	
	public static function getBookCharts($bID, $limit = 5){
		// Get all charts associated with this book ID.
		/*
		SELECT chID, chart.group_gID, name, description, group_user.isAdmin AS groupAdmin, thumbnail, book_chart.position AS chartPosition, IF(originator.uID IS NULL, owner.userName, originator.userName) AS belongsTo
		FROM picboard.chart 
		LEFT JOIN group_user ON chart.group_gID=group_user.group_gID 
		LEFT JOIN user AS originator ON chart.clone_of = originator.uID
		LEFT JOIN user AS owner ON chart.user_uID = owner.uID
		RIGHT JOIN book_chart ON book_chart.chart_chID=chart.chID
		WHERE book_chart.book_bID=5
		LIMIT 0, 1
		*/
		$dbCon = db::singleton();
		
		$sql = "SELECT chID, chart.group_gID, name, description, group_user.isAdmin AS groupAdmin, thumbnail, book_chart.position AS chartPosition, IF(originator.uID IS NULL, owner.userName, originator.userName) AS belongsTo FROM " . SYS_DB_DBNAME . ".chart LEFT JOIN group_user ON chart.group_gID=group_user.group_gID LEFT JOIN user AS originator ON chart.clone_of = originator.uID LEFT JOIN user AS owner ON chart.user_uID = owner.uID RIGHT JOIN book_chart ON book_chart.chart_chID=chart.chID WHERE book_chart.book_bID=" . $dbCon->real_escape_string($bID) . " ORDER BY position ASC LIMIT 0, " . $dbCon->real_escape_string($limit);
		
		$result = $dbCon->single( $sql );
		
		return $result;	
	}

	public static function getChartLibrary($chID, $returnData = false){
		/*
		SELECT IF(group.gID IS NULL, 'My Charts' , group.name) AS library
		FROM picboard.chart
		LEFT JOIN picboard.group ON chart.group_gID=group.gID
		WHERE chart.chID=27;
		*/
		
		$dbCon = db::singleton();
		
		$sql = "SELECT IF(group.gID IS NULL, 'My Charts' , group.name) AS library, gID, group.name FROM " . SYS_DB_DBNAME . ".chart LEFT JOIN " . SYS_DB_DBNAME . ".group ON chart.group_gID=group.gID WHERE chart.chID=" . $dbCon->real_escape_string((int)$chID) . ";";
		
		$result = $dbCon->oneRow( $sql );
		
		return ($returnData) ? $result : $result['library'];
	}

	public static function chartExistsAndModifiable(user &$userObj, $chartInfo){
		// Does the user have permissions to edit this chart?
		/*
		SELECT (SELECT COUNT(*) FROM group_user WHERE group_user.user_uID=chart.user_uID AND group_user.group_gID = chart.group_gID) AS belongsToGroup, isAdmin
		FROM picboard.chart
		LEFT JOIN group_user ON chart.group_gID=group_user.group_gID
		WHERE chart.user_uID=1 AND chart.chID=1
		*/
		$dbCon = db::singleton();
		
		$sql = "SELECT (SELECT COUNT(*) FROM group_user WHERE group_user.user_uID=chart.user_uID AND group_user.group_gID = chart.group_gID) AS belongsToGroup, isAdmin, clone_of FROM " . SYS_DB_DBNAME . ".chart LEFT JOIN group_user ON chart.group_gID=group_user.group_gID WHERE chart.user_uID=" . $dbCon->real_escape_string($userObj->getuID()) . " AND chart.chID=" . $dbCon->real_escape_string($chartInfo['chartID']);
		
		$result = $dbCon->oneRow( $sql );
		
		if(count($dbCon) > 0){
			if($result['belongsToGroup'] == 0){
				return array("status" => "success");
			} else {
				if($result['isAdmin'] != "") return array("status" => "success");
			}
		
		} else {
			return array("status" => "failure", "reason" => "NoChartFound");
		}
		
		return array("status" => "failure", "reason" => "NoPermissions");
	}


	public static function updateChartDetails(user &$userObj, $chartID, $name, $description){
		if(chart::chartExistsAndModifiable($userObj, array('chartID' => $chartID)) == false) throw new PicBoardException("Cannot update chart as you do not have permissions");
		
		$dbCon = db::singleton();
		
		$sql = "UPDATE " . SYS_DB_DBNAME . ".chart SET name='" . $dbCon->real_escape_string($name) . "', description='" . $dbCon->real_escape_string($description) . "' WHERE chID=" . (int)$chartID;
		$dbCon->single($sql);
		
		return true;
	}

	public static function updateChart(user &$userObj, $chart){
		$dbCon = db::singleton();
		
		// 1. Update chart information.
		// 2. Update every cell associated with the chart.
		$dbCon->startTransaction();
		
		try {
			// Step 1
			/*$rows = (int)$chart['cellSettings'][ count($chart['cellSettings'])-1 ]['gridRef'][0];
			$columns = (int)$chart['cellSettings'][ count($chart['cellSettings'])-1 ]['gridRef'][1];
			*/
			$rows = (int)$chart['chartSettings']['rows'];
			$columns = (int)$chart['chartSettings']['columns'];
			
			$visibility = ($chart['chartSettings']['visibility'] == 'visible') ? 0 : 1;
			
			if($chart['chartSettings']['title'] == ""){
				// The chart name cannot be null.
				return array("status" => "failure");
			}
		
			$chartID = $dbCon->real_escape_string($chart['chartSettings']['chartID']);
			
			// overlay
			if(in_array($chart['chartSettings']['overlay'], unserialize(REGISTERED_OUTPUTS))){
				$style = $chart['chartSettings']['overlay'];
			} else {
				$style = "defaultFormat";
			}
			
			
			$sql = "UPDATE " . SYS_DB_DBNAME . ".chart SET name='" . $dbCon->real_escape_string($chart['chartSettings']['title']) . "', description='" . $dbCon->real_escape_string($chart['chartSettings']['description']) . "', columns=" . $dbCon->real_escape_string($columns) . ", rows=" . $dbCon->real_escape_string($rows) . ", private=" . $visibility . ", style='" . $dbCon->real_escape_string($style) . "', clone_of=NULL WHERE chID=" . $chartID;

			$dbCon->oneRow( $sql );
		
			// Delete current cells
			$sql = "DELETE FROM " . SYS_DB_DBNAME . ".cell WHERE chart_chID=" . $chartID;
			$dbCon->oneRow($sql);
		
		
			// Sort the cells by location.
			//usort($chart['cellSettings'], array("chart", "sortCells"));
		
			// Update the cells.
			$createCells = "INSERT INTO " . SYS_DB_DBNAME . ".cell (chart_chID, image_imgID, cellLocation, text, textLocation, textAlignment, textSize, textColour, bgColour, textHeight, frameColour, mulberryURL, fontFace, textBold, textItalic, textUnderline) VALUES ";
			
			foreach($chart['cellSettings'] as $cell){
				$source = ($cell['imageID'] == 0) ? "NULL" : $cell['imageID'];
				$cell['image'] = str_replace(SYS_BASEURL . "SVGProxy.php?r=", "http://", $cell['image']);
				$mulberryURL = ($source == "NULL") ? $dbCon->real_escape_string($cell['image']) : '';
				$location = $rows * $cell['gridRef'][1] - ( $rows - $cell['gridRef'][0] );
				
				$cell['PBCbold'] = ($cell['PBCbold'] == "true") ? 1 : 0;
				$cell['PBCitalic'] = ($cell['PBCitalic'] == "true") ? 1 : 0;
				$cell['PBCunderline'] = ($cell['PBCunderline'] == "true") ? 1 : 0;
				
				$createCells .= "($chartID, $source, $location, '" . $dbCon->real_escape_string($cell['text']) . "', '" . $dbCon->real_escape_string($cell['textLocation']) . "', '" . $dbCon->real_escape_string($cell['textAlignment']) . "', " . $dbCon->real_escape_string($cell['textSize']) . ", '" . $dbCon->real_escape_string($cell['colour']) . "', '" . $dbCon->real_escape_string($cell['bgcolour']) . "', " . $dbCon->real_escape_string($cell['textHeight']) . ", '" . $dbCon->real_escape_string($cell['frameColour']) . "', '$mulberryURL', '" . $dbCon->real_escape_string($cell['fontFace']) . "', '" . $dbCon->real_escape_string($cell['PBCbold']) . "', '" . $dbCon->real_escape_string($cell['PBCitalic']) . "', '" . $dbCon->real_escape_string($cell['PBCunderline']) . "'), ";
			}
		
			$createCells = rtrim($createCells, ", ");
		
			$dbCon->oneRow( $createCells );	
		
			$dbCon->commit();
			
			// Create thumbnails
			$thumb = new thumbnail($userObj, (int)$chartID);
			$thumb->createThumbnail();			
			
			return array("status" => "success");
		} catch (Exception $e){
			$dbCon->rollback();
			var_dump($e);
			return array("status" => "failure");
		}
		
	}

	public static function sortCells($a, $b){
		
        $al = $a['gridRef'][0] * $a['gridRef'][1]-1;
        $bl = $b['gridRef'][0] * $b['gridRef'][1]-1;
        
        if ($al == $bl) {
            return 0;
        }
        return ($al > $bl) ? +1 : -1;
    }

	public static function removeCellImageReference(user &$userObj, $cellID){
		// Note this only deletes references. Orphans images.
		// Only use for donating, otherwise we'll get a bunch of orphaned images.
		$dbCon = db::singleton();
		
		$sql = "UPDATE " . SYS_DB_DBNAME . ".cell SET image_imgID=NULL WHERE ceID=" . $dbCon->real_escape_string((int) $cellID);
		
		$dbCon->single($sql);
	}

	public static function getGroupChartFromOriginalID($chartID, $group_ID){
		$dbCon = db::singleton();
	
		$sql = "SELECT chID FROM " . SYS_DB_DBNAME . ".chart WHERE group_gID=" . $dbCon->real_escape_string((int)$group_ID) . " AND clone_of=" . $dbCon->real_escape_string((int)$chartID);

		$result = $dbCon->oneRow( $sql );

		return (int) $result['chID'];	
	}	

	public static function duplicateChart(user &$userObj, $chartID, $newName, $location = chart::LOCATION_LOCAL, $locationID = 0){
		/*
		INSERT INTO picboard.chart (user_uID, group_gID, name, description, columns, rows, private)
		SELECT 1, NULL, name, description, columns, rows, private
		FROM picboard.chart
		WHERE chID=18		
		
		INSERT INTO picboard.cell (chart_chID, image_imgID, cellLocation, text, textLocation, textAlignment, textSize, textBold, textItalic, textUnderline, fontFace, textColour, bgColour, textHeight, frameColour, mulberryURL)
		SELECT 9999, image_imgID, cellLocation, text, textLocation, textAlignment, textSize, textBold, textItalic, textUnderline, fontFace, textColour, bgColour, textHeight, frameColour, mulberryURL
		FROM picboard.cell
		WHERE chart_chID = 18;		
		*/
		$chartID = (int)$chartID;
		if(is_int($chartID) == false) throw new PicBoardException("Chart ID is not an integer");	
		
		// Can we read the chart?
		$chartData = chart::getChart($userObj, $chartID);
		
		// Are we allowed to see this?
		if($chartData['status'] == "success"){
			
			$dbCon = db::singleton();
			$dbCon->startTransaction();
			
			// Is the clone_of ID null? If it is we need to set it to $chartID, otherwise leave it as it is.
			$cloneID = ($chartData['data']['chart']['clone_of'] == "") ? (int) $chartID : (int) $chartData['data']['chart']['clone_of'];
			
			if($newName == "") $newName = $chartData['data']['chart']['name'];
			
			try {
			
				switch($location){
					case chart::LOCATION_LOCAL:
						$sql = "INSERT INTO " . SYS_DB_DBNAME . ".chart (user_uID, group_gID, originator_uID, name, description, columns, rows, private, clone_of) SELECT " . $dbCon->real_escape_string($userObj->getuID()) . ", NULL, originator_uID, \"" . $dbCon->real_escape_string($newName) . "\", description, columns, rows, private, " . $dbCon->real_escape_string($cloneID) . " FROM " . SYS_DB_DBNAME . ".chart WHERE chID=" . $dbCon->real_escape_string($chartID);
					break;
					
					case chart::LOCATION_GROUP:
						// Save to a group's library
						$sql = "INSERT INTO " . SYS_DB_DBNAME . ".chart (user_uID, group_gID, originator_uID, name, description, columns, rows, private, clone_of) SELECT NULL, '" . $dbCon->real_escape_string((int)$locationID) . "', originator_uID, \"" . $dbCon->real_escape_string($newName) . "\", description, columns, rows, private, " . $dbCon->real_escape_string($cloneID) . " FROM " . SYS_DB_DBNAME . ".chart WHERE chID=" . $dbCon->real_escape_string($chartID);
					break;
					
					case chart::LOCATION_USER:
						// Save to another user's library.
						$sql = "INSERT INTO " . SYS_DB_DBNAME . ".chart (user_uID, group_gID, originator_uID, name, description, columns, rows, private, clone_of) SELECT " . $dbCon->real_escape_string((int)$locationID) . ", NULL, originator_uID, \"" . $dbCon->real_escape_string($newName) . "\", description, columns, rows, private, " . $dbCon->real_escape_string($cloneID) . " FROM " . SYS_DB_DBNAME . ".chart WHERE chID=" . $dbCon->real_escape_string($chartID);
					break;
					
					case chart::LOCATION_PUBLIC:
						$sql = "INSERT INTO " . SYS_DB_DBNAME . ".chart (user_uID, group_gID, originator_uID, name, description, columns, rows, private, clone_of) SELECT NULL, NULL, originator_uID, \"" . $dbCon->real_escape_string($newName) . "\", description, columns, rows, private, " . $dbCon->real_escape_string($cloneID) . " FROM " . SYS_DB_DBNAME . ".chart WHERE chID=" . $dbCon->real_escape_string($chartID);
					break;
					
					default:
						throw new PicBoardException("Location has not been set. default is chart::LOCATION_LOCAL.");
					break;
				}
				
				$dbCon->single($sql);
				
				// Get the ID of the new chart
				$newID = $dbCon->insert_id;
								
				$sql = "INSERT INTO " . SYS_DB_DBNAME . ".cell (chart_chID, image_imgID, cellLocation, text, textLocation, textAlignment, textSize, textBold, textItalic, textUnderline, fontFace, textColour, bgColour, textHeight, frameColour, mulberryURL) SELECT " . $newID . ", image_imgID, cellLocation, text, textLocation, textAlignment, textSize, textBold, textItalic, textUnderline, fontFace, textColour, bgColour, textHeight, frameColour, mulberryURL FROM " . SYS_DB_DBNAME . ".cell WHERE chart_chID = " . $dbCon->real_escape_string($chartID) . ";";
								
				$dbCon->single($sql);
				
				$dbCon->commit();
				
				// Create thumbnails
				$thumb = new thumbnail($userObj, $newID);
				$thumb->createThumbnail();
				
				return array("status" => "success", "chartID" => $newID);
			} catch (Exception $e){
				$dbCon->rollback();
				return array("status" => "failure", "reason" => "Commit error", "error_message" => $e->getMessage());
			}
		} else {
			return array("status" => "failure", "reason" => "ChartAccessDenied");
		}
	}
	
	public static function saveChartAs(user &$userObj, $chartID, array $details){
		// We're duplicating the original chart (defined in $chartID), and creating a new one with those base details, then modified using $newDetails.
		
		$chartID = (int)$chartID;
		if(is_int($chartID) == false) throw new PicBoardException("Chart ID is not an integer");
		
		$dbCon = db::singleton();
		
		$dbCon->startTransaction();
		
		try {
		
			//print_r($details);
			
			// Steps for this process:
			// 1. Move any images that have been donated to the group to the group's area (if this is going to a chart).
			
			$details['chartSettings']['groupID'] = (int)$details['chartSettings']['groupID'];
			

			// Is this chart in a group?
			if($details['chartSettings']['groupID'] != 0 && is_int($details['chartSettings']['groupID'])){
				// Check each cell to see if they contain images that need to be moved.
				
				$imagesetID = group::getGroupImportedImageSet($userObj, $details['chartSettings']['groupID']);
				
				foreach($details['cellSettings'] as &$cell){
					// Is this image stored in a users imagestore?
					if(preg_match("/\/imagestore\/\d+\//i", $cell['image'])){
	 					
						// Pull out the path
						$path = util::urltopath($cell['image']);
						
						$filename = util::filenameNoExt($path);
						
						$newFilename = md5($filename . time());
						
						$newPath = str_replace($filename, $newFilename, $path);
						$newPath = preg_replace("/\/imagestore\/(\d+)\//i", "/imageStore/g-" . $details['chartSettings']['groupID'] . "/", $newPath);
						
						if(copy($path, $newPath) == false) throw new PicBoardException("Cannot copy to group images directory.");
						
						// Create the record in picboard.image
						$sql = "INSERT INTO " . SYS_DB_DBNAME . ".image (imageURL, name, private) VALUES('" . $dbCon->real_escape_string( util::pathtourl($newPath) ) . "' ,'Photo', 1)";
						$dbCon->single($sql);
						
						$imageID = $dbCon->insert_id;
						
						// Create the record in picboard.image_imageset
						$sql = "INSERT INTO " . SYS_DB_DBNAME . ".image_imageset (image_imgID, imageset_isID) VALUES(" .  $imageID . ", " . $imagesetID . ")";
						$dbCon->single($sql);
						
						// Set the cell imageID to the new picboard.image record
						$cell['imageID'] = $imageID;
						
					}
				}
			}
		
			$dbCon->commit();
			return chart::createChart($userObj, $details);
		
		} catch (Exception $e){
			$dbCon->rollback();
			echo $e->getTraceAsString();
			return array("status" => "failure", "reason" => "Commit error", "error_message" => $e->getMessage());
		}
		
		// 2. 'create' chart with 'new' settings
	}


	public static function canCreateChart(user &$userObj, $chartInfo){
		// We're creating a new chart, so see if there is one with the same name in the user's library, or in another library
		
		/*
		SELECT COUNT(*), IF(group_gID, 0, 1) AS personal
		FROM picboard.chart
		WHERE user_uID=1 AND name='Test Chart'
		GROUP BY user_uID;
		*/
		
		$dbCon = db::singleton();
		
		// Does the chart belong to the user? If so, do they have permission to create a chart with this name?
		
		if($chartInfo['groupID'] == 0){

			$sql = "SELECT COUNT(*) AS chartExists, IF(group_gID, 0, 1) AS personal FROM " . SYS_DB_DBNAME . ".chart WHERE user_uID=" . $dbCon->real_escape_string( $userObj->getuID() ) . " AND name='" . $dbCon->real_escape_string($chartInfo['title']) . "' GROUP BY user_uID;";
			$result = $dbCon->oneRow( $sql );
			
			if($result == null){

				// The user can create this chart.
				return array("status" => true);
			} else {
				// There's a conflict

				// Is the conflict in this users library?
				if((int)$result['personal'] == 1 && (int)$result['chartExists'] == 1){
					return array("status" => false, "reason" => "You cannot save this chart as there is a chart with the same name in your personal library. Please choose another." );
				}
				
				// Is the conflict in a group that this user is in?
				if($result['personal'] == 0 && $result['chartExists'] == 1){
					return array("status" => false, "reason" => "A chart exists in this library with the same name" );
				}			
			}
			
		} else {

			$sql = "SELECT COUNT(*) AS chartExists, IF(group_gID, 0, 1) AS personal FROM " . SYS_DB_DBNAME . ".chart WHERE group_gID=" . $dbCon->real_escape_string( $chartInfo['groupID'] ) . " AND name='" . $dbCon->real_escape_string($chartInfo['title']) . "' GROUP BY group_gID;";
			$result = $dbCon->oneRow( $sql );
			
			if($result == null){
				// The user can create this chart.
				return array("status" => true);
			} else {
				// There's a conflict
				
				// Is the conflict in this users library?
				if($result['personal'] == 1 && $result['chartExists'] == 1){
					return array("status" => false, "reason" => "DuplicateNamePersonalLibrary" );
				}
				
				// Is the conflict in a group that this user is in?
				if($result['personal'] == 0 && $result['chartExists'] == 1){
					return array("status" => false, "reason" => "DuplicateNameGroupLibrary" );
				}			
			}

		}
		
		return array("status" => true);
	}
	
	public static function createChart(user &$userObj, $chart){
		// We're creating a new chart. This involves:
		// 1. Create the new chart in the chart table associated with current userID. Is this part of a book, too?
		// 2. Are we creating a group chart? If so, we need to link it to the group.
		// 3. Fetch insert_id
		// 4. Loop every cell inserting, and insert the insert_id from the chart to link them.
		// 5. If there is an image present, check for availability in the global imageset. If it's not there, add it to the user's uncategorised images (unless its already in one of the users imageset).
		
		$dbCon = db::singleton();
		
		$dbCon->startTransaction();
		
		// Step 1, 2, 3
		try {
			$book_id = ($chart['chartSettings']['bookID'] == 0) ? "" : $dbCon->real_escape_string($chart['chartSettings']['bookID']);
			$group_id = ($chart['chartSettings']['groupID'] == 0) ? "NULL" : $dbCon->real_escape_string($chart['chartSettings']['groupID']);
			$visibility = ($chart['chartSettings']['visibility'] == 'visible') ? 0 : 1;
			
			
			/*$rows = (int)$chart['cellSettings'][ count($chart['cellSettings'])-1 ]['gridRef'][0];
			$columns = (int)$chart['cellSettings'][ count($chart['cellSettings'])-1 ]['gridRef'][1];
			*/
			$rows = (int)$chart['chartSettings']['rows'];
			$columns = (int)$chart['chartSettings']['columns'];
			
			// overlay
			if(in_array($chart['chartSettings']['overlay'], unserialize(REGISTERED_OUTPUTS))){
				$style = $chart['chartSettings']['overlay'];
			} else {
				$style = "defaultFormat";
			}
			
			$createChart = "INSERT INTO " . SYS_DB_DBNAME . ".chart (user_uID, originator_uID, group_gID, name, description, columns, rows, private, thumbnail, style) VALUES (" . $userObj->getuID() . ", " . $userObj->getuID() . ", $group_id, '" . $dbCon->real_escape_string($chart['chartSettings']['title']) . "', '" . $dbCon->real_escape_string($chart['chartSettings']['description']) . "', " . $dbCon->real_escape_string($columns) . ", " . $dbCon->real_escape_string($rows) . ", $visibility, '" . md5(time() . rand(0, 99999)) . ".jpg', '" . $dbCon->real_escape_string($style) . "');";
			//echo $createChart;
			$dbCon->oneRow( $createChart );
			
			$chartID = $dbCon->insert_id;
			
			if($book_id != ""){
				// Create link to book id
				$createBookLink = "INSERT INTO " . SYS_DB_DBNAME . ".book_chart (chart_chID, book_bID) VALUES(" . $dbCon->real_escape_string($chartID) . "," . $dbCon->real_escape_string($book_id) . ")";
				$dbCon->single( $createBookLink );
			}
			
			
			// Step 4. Create the cells
			$createCells = "INSERT INTO " . SYS_DB_DBNAME . ".cell (chart_chID, image_imgID, cellLocation, text, textLocation, textAlignment, textSize, textColour, bgColour, textHeight, frameColour, mulberryURL, fontFace, textBold, textItalic, textUnderline) VALUES ";
			
			foreach($chart['cellSettings'] as $cell){
				$source = ($cell['imageID'] == 0) ? "NULL" : $cell['imageID'];
				$mulberryURL = ($source == "NULL") ? $dbCon->real_escape_string($cell['image']) : '';
				$mulberryURL = str_replace(SYS_BASEURL . "SVGProxy.php?r=", "http://", $cell['image']);
				
				$location = isset($cell['absolutePos']) ? $cell['absolutePos'] : $rows * $cell['gridRef'][1] - ( $rows - $cell['gridRef'][0] );
				$createCells .= "($chartID, $source, $location, '" . $dbCon->real_escape_string($cell['text']) . "', '" . $dbCon->real_escape_string($cell['textLocation']) . "', '" . $dbCon->real_escape_string($cell['textAlignment']) . "', " . $dbCon->real_escape_string($cell['textSize']) . ", '" . $dbCon->real_escape_string($cell['colour']) . "', '" . $dbCon->real_escape_string($cell['bgcolour']) . "', " . $dbCon->real_escape_string($cell['textHeight']) . ", '" . $dbCon->real_escape_string($cell['frameColour']) . "', '$mulberryURL', '" . $dbCon->real_escape_string($cell['fontFace']) . "', '" . $dbCon->real_escape_string($cell['PBCbold']) . "', '" . $dbCon->real_escape_string($cell['PBCitalic']) . "', '" . $dbCon->real_escape_string($cell['PBCunderline']) . "'), ";
			}
		
			$createCells = rtrim($createCells, ", ");
		
			$dbCon->oneRow( $createCells );
			//echo $createCells;
			// Commit changes to database
			$dbCon->commit();
			
			// Create thumbnails
			$thumb = new thumbnail($userObj, $chartID);
			$thumb->createThumbnail();
			
			
			return array("status" => "success", "chartID" => $chartID);
		
		} catch (Exception $e){
			$dbCon->rollback();
			return array("status" => "failure", "reason" => "Commit error", "error_message" => $e->getMessage());
		}
		
	}
	
	public static function getChart(user &$userObj, $chartID, $forEditing = false){
		// Step 1: Get chart.
		// Step 2: Get cells.
		
		/*		
		SELECT chart.chID, chart.name, chart.description, chart.user_uID AS ownerID, chart.columns, chart.rows, chart.private, 
		group.gID AS groupID, group.name AS groupName, group.private AS inPrivateGroup, book.bID as bookID, book.name AS bookName, user.firstName, user.lastName, 
		(SELECT COUNT(*) FROM picboard.user_share_chart WHERE user_share_chart.chart_chID=chart.chID AND user_share_chart.uID_from=chart.user_uID AND user_share_chart.uID_to=1) AS explicitShare
		FROM picboard.chart
		LEFT JOIN picboard.group ON chart.group_gID = group.gID
		LEFT JOIN picboard.book_chart ON chart.chID=book_chart.chart_chID
		LEFT JOIN picboard.book ON book_chart.book_bID = book.bID
		LEFT JOIN picboard.user ON chart.user_uID = user.uID
		WHERE chart.chID=30;
		*/
		
		$chartID = (int)$chartID;
		
		if(is_int($chartID) == false) throw new PicBoardException("Chart ID is not an integer");
		
		$dbCon = db::singleton();
		
		$sql = "SELECT chart.chID, chart.name, chart.description, chart.columns, chart.rows, chart.private, chart.style, chart.clone_of, group.gID AS groupID, group.name AS groupName, group.private AS inPrivateGroup, book.bID AS bookID, book.name AS bookName, user.uID AS ownerID, user.firstName, user.lastName, (SELECT COUNT(*) FROM " . SYS_DB_DBNAME . ".user_share_chart WHERE user_share_chart.chart_chID=chart.chID AND user_share_chart.uID_from=chart.user_uID AND user_share_chart.uID_to=" . $userObj->getuID() . ") AS explicitShare FROM " . SYS_DB_DBNAME . ".chart LEFT JOIN " . SYS_DB_DBNAME . ".group ON chart.group_gID = group.gID LEFT JOIN " . SYS_DB_DBNAME . ".book_chart ON chart.chID=book_chart.chart_chID LEFT JOIN " . SYS_DB_DBNAME .  ".book ON book_chart.book_bID = book.bID LEFT JOIN " . SYS_DB_DBNAME . ".user ON chart.user_uID = user.uID WHERE chart.chID=" . $dbCon->real_escape_string($chartID) . ";";
		
		$chart = $dbCon->oneRow( $sql );
		
		// Get cells
		
		$sql = "SELECT cell.*, image.imageURL FROM " . SYS_DB_DBNAME . ".cell LEFT JOIN " . SYS_DB_DBNAME . ".image ON cell.image_imgID=image.imgID WHERE cell.chart_chID=" . $dbCon->real_escape_string($chartID) . " ORDER BY ceID;";
		
		$cells = $dbCon->single( $sql );
		
		// Security checks.
		
		// Is the chart in the public library?
		if( $chart['groupID'] == "" && $chart['ownerID'] == "") return array("status" => "success", "data" => array("chart" => $chart, "cells" => $cells, "sharedChart" => true, "sharedChart" => true, "publicChart" => true) );
		
		// Is this the user's chart?
		if( $chart['ownerID'] != $userObj->getuID() ){
			// This is not the user's chart.

			// Is the chart in a group?
			if( $chart['groupName'] != ""){
				// Is this user in the group? or is the group open?
				if($userObj->isInGroup( $chart['groupID'] ) == false && $chart['inPrivateGroup'] == true || $userObj->isInGroup( $chart['groupID'] ) == false && $chart['private'] == "1" ){
					return array("status" => "failure", "reason" => "PrivateGroup" );
				} else {
					return array("status" => "success", "data" => array("chart" => $chart, "cells" => $cells, "sharedChart" => true, "sharedChart" => true) );
				}
			} else {
				// Chart belongs to another user.
				
				if($forEditing) return array("status" => "failure", "reason" => "PermissionDenied");
				
				// Has the user shared it with this user?
				if($chart['explicitShare'] == 1) return array("status" => "success", "data" => array("chart" => $chart, "cells" => $cells, "sharedChart" => true) );
				
				// Is it private? If not we can display it.
				if( $chart['private'] == 0 ){
					return array("status" => "success", "data" => array("chart" => $chart, "cells" => $cells, "sharedChart" => false) );
				} else {
					return array("status" => "failure", "reason" => "PrivateUser");
				}		
			}

		
		} else {
			// This is the user's chart.
			return array("status" => "success", "data" => array("chart" => $chart, "cells" => $cells) );
		}
		
	}
	
	public static function deleteChart(user &$userObj, $chartID){
		/*
		SELECT chart.chID, chart.user_uID AS ownerID, 
		group.gID AS groupID, group_user.isAdmin, group_user.active
		FROM picboard.chart
		LEFT JOIN picboard.group ON chart.group_gID = group.gID
		LEFT JOIN picboard.group_user ON chart.group_gID=group_user.group_gID AND chart.user_uID=group_user.user_uID
		WHERE chart.chID=30;
		*/
		
		$chartID = (int)$chartID;
		if(is_int($chartID) == false) throw new PicBoardException("Chart ID is not an integer");
		
		$dbCon = db::singleton();
		
		// Check to see if the user has permissions to delete this chart.
		$sql = "SELECT chart.chID, chart.user_uID AS ownerID, chart.clone_of, group.gID AS groupID, group_user.isAdmin, group_user.active FROM " . SYS_DB_DBNAME . ".chart LEFT JOIN " . SYS_DB_DBNAME . ".group ON chart.group_gID = group.gID LEFT JOIN " . SYS_DB_DBNAME . ".group_user ON chart.group_gID=group_user.group_gID AND chart.user_uID=group_user.user_uID WHERE chart.chID=" . $dbCon->real_escape_string($chartID) . ";";
		
		$result = $dbCon->oneRow($sql);
		
		if(count($result) > 0){
			// Does this chart belong to the current user?
			if($result['ownerID'] != $userObj->getuID()){
				// This isnt the users chart. If its in a group and this user is active and an admin, allow them to perform this function. Else show an error.
				if($result['groupID'] == ""){
					return array("status" => false, "reason" => "InsufficientPermissions");
				} else {
					if((int)$result['isAdmin'] != 1 && (int)$result['active'] != 1){
						// If this is a clone of a chart that the current user has we need to let them delete it.
						if($result['clone_of'] != ""){
							$ownerID = chart::chartOwnerID($result['clone_of']);
							if((int)$ownerID['user_uID'] != $userObj->getuID()){
								return array("status" => false, "reason" => "Insufficient Group Permissions + not originator");
							}
						} else {
							return array("status" => false, "reason" => "InsufficientGroupPermissions");
						}
						
					}
				}
				
			}
			
			// Security checks passed.
			
			$dbCon->startTransaction();
			
			try {
			
				// First, delete all the cells.
				$sql = "DELETE FROM " . SYS_DB_DBNAME . ".cell WHERE chart_chID=" . $dbCon->real_escape_string($chartID) . ";";
			
				$dbCon->queuedQuery($sql);
			
				// Delete from books.
				$sql = "DELETE FROM " . SYS_DB_DBNAME . ".book_chart WHERE chart_chID=" . $dbCon->real_escape_string($chartID) . ";";
				
				$dbCon->queuedQuery($sql);

				// Now delete the chart itself.
				$sql = "DELETE FROM " . SYS_DB_DBNAME . ".chart WHERE chID=" . $dbCon->real_escape_string($chartID) . ";";

				$dbCon->queuedQuery($sql);
				
				$dbCon->runBatch();

				$thumb = new thumbnail($userObj, $chartID);
				
				$thumb->deleteThumbnail($userObj, (int)$chartID);
				
				$dbCon->commit();
				
				return array("status" => true);
			} catch(Exception $e){
				$dbCon->rollback();
				print_r($e->getTrace());
				return array("status" => false, "reason" => "UnableToDelete", "error" => $e->getMessage());
			}
				
		} else {
			return array("status" => false, "reason" => "InvalidChart");
		}
	}

}

?>